USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerStatsSelectByPlayerId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerStatsSelectByPlayerId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	PlayerStats Table Select By PlayerId
-- =============================================
CREATE PROCEDURE PlayerStatsSelectByPlayerId

			@PlayerId varchar(30)
	
AS
BEGIN

DECLARE 
		@ReturnName varchar(50),
		@Kills int,
		@Assists int,
		@Deaths int,
		@KillDeathRatio float,
		@Destructions int,
		@BuiltObjects int,
		@Dominations int,
		@Revenges int,
		@Ubers int,
		@Backstabs int,
		@Headshots int,
		@Captures int,
		@CaptureBlocks int,
		@FlagsCaptured int,
		@FlagsTouched int,
		@FlagsDefended int
		

select @Assists = COUNT(AssistId) FROM Assists WHERE assister_PlayerId = @PlayerId

exec PlayerNameListSelectNameById @PlayerId, @ReturnName OUTPUT

select @Kills = COUNT(KillId) FROM Kills WHERE Attacker_PlayerId = @PlayerId

select @Deaths = COUNT(KillId) FROM Kills WHERE Victim_PlayerId = @PlayerId
	AND Victim_classId != (select top 1 ClassId from classes WHERE ClassShortName = 'medic')

select @Destructions = COUNT(DestructionId) FROM Destructions WHERE Attacker_PlayerId = @PlayerId
select @BuiltObjects = COUNT(EngineeringId) FROM Engineering WHERE PlayerId = @PlayerId
select @Captures = COUNT(CaptureListId) FROM CaptureList 
		WHERE PlayerId = @PlayerId


select @CaptureBlocks = COUNT(CaptureBlockId) FROM CaptureBlocks 
		WHERE PlayerId = @PlayerId

select @FlagsCaptured = COUNT(FlagId) FROM Flag 
		WHERE PlayerId = @PlayerId
		AND FlagEventId = (SELECT FlagEventId FROM FlagEventType WHERE FlagEventShortName = 'captured')

select @FlagsTouched = COUNT(FlagId) FROM Flag 
		WHERE PlayerId = @PlayerId
		AND FlagEventId = (SELECT FlagEventId FROM FlagEventType WHERE FlagEventShortName = 'picked up')

select @FlagsDefended = COUNT(FlagId) FROM Flag 
		WHERE PlayerId = @PlayerId
		AND FlagEventId = (SELECT FlagEventId FROM FlagEventType WHERE FlagEventShortName = 'defended')

select @Ubers  = COUNT(ChargeId) FROM MedicCharge
		WHERE PlayerId = @PlayerId

select @Dominations  = COUNT(DominationId) FROM Dominations
		WHERE Attacker_PlayerId = @PlayerId

select @Revenges  = COUNT(RevengeId) FROM Revenges
		WHERE Attacker_PlayerId = @PlayerId

select @Backstabs = COUNT(KillId) FROM Kills
		WHERE Attacker_PlayerId = @PlayerId
		AND CustomKillId = (SELECT CustomKillId FROM CustomKills WHERE CustomKillShortName = 'backstab')

select @Headshots = COUNT(KillId) FROM Kills
		WHERE Attacker_PlayerId = @PlayerId
		AND CustomKillId = (SELECT CustomKillId FROM CustomKills WHERE CustomKillShortName = 'headshot')


select @ReturnName as [PlayerName], 0 as [Points], 
		@Kills as [Kills], @Assists as [Assists], 
		@Deaths as [Deaths], 0.0 as [KillDeathRatio],
		@Destructions as [Destructions], @BuiltObjects as [BuiltObjects], 
		@Dominations as [Dominations], @Revenges as [Revenges],
		@Ubers as [Ubers], @Backstabs as [Backstabs], @Headshots as [Headshots],
		@Captures as [Captures], @CaptureBlocks as [CaptureBlocks], 
		@FlagsCaptured as [FlagsCaptured], @FlagsTouched as [FlagsTouched],
		@FlagsDefended as [FlagsDefended], 
		@PlayerId as [PlayerId]

END
GO

--  PlayerStatsSelectByPlayerId 'STEAM_0:0:13191839'
